﻿using ModelCoding.Entity;
using ModelCoding.IServices;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;

namespace ModelCoding.Services
{
    public class DbServices : IDbServices
    {

        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public BaseStatus OpenConn(string Server,string DataBaseName, string UserName, string PassWord)
        {
            BaseStatus bs = new BaseStatus();
            string ConnStrings = $"Data Source={Server};Initial Catalog={DataBaseName};Persist Security Info=True;User ID={UserName};Password={PassWord}";
            try
            {
                conn = new SqlConnection(ConnStrings);
                conn.Open();
            }
            catch (Exception ex)
            {
                bs.Code = -1;
                bs.Message = ex.Message;
            }
            return bs;
        }

        public async Task<BaseStatus> GetTable(string Server, string DataBaseName, string UserName, string PassWord)
        {
            BaseStatus bs = new BaseStatus();
            try
            {
                var res = OpenConn(Server, DataBaseName, UserName, PassWord);
                if (res.Code==200)
                {

                    List<TableModel> list = new List<TableModel>();
                    string sql = "select ROW_NUMBER() over(order by TABLE_NAME) as Number,TABLE_NAME as TableName from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'";
                    cmd = new SqlCommand(sql, conn);
                    SqlDataReader sdr = await cmd.ExecuteReaderAsync();

                    while (await sdr.ReadAsync())
                    {
                        TableModel model = new TableModel
                        {
                            TableName = sdr["TableName"].ToString(),
                            Number = Convert.ToInt32(sdr["Number"]),
                            IsChecked=true
                        };
                        list.Add(model);
                    }

                    bs.Data = list;
                }
                else
                {
                    bs.Code = -1;
                    bs.Message = res.Message;
                }
            }
            catch (Exception ex)
            {
                bs.Code = -1;
                bs.Message = ex.Message;
            }
            return bs;
        }

        public async Task<BaseStatus> GetDataBase(string Server, string UserName, string PassWord)
        {
            BaseStatus bs = new BaseStatus();
            try
            {
                var res = OpenConn(Server, "master", UserName, PassWord);
                if (res.Code==200)
                {
                    List<DataBaseModel> list = new List<DataBaseModel>();
                    string sql = "select ROW_NUMBER() over(order by crdate desc) as Number,name,crdate from SysDatabases";
                    cmd = new SqlCommand(sql, conn);
                    SqlDataReader sdr = await cmd.ExecuteReaderAsync();

                    while (await sdr.ReadAsync())
                    {
                        DataBaseModel model = new DataBaseModel
                        {
                            Name = sdr["Name"].ToString(),
                            CrDate = sdr["CrDate"].ToString(),
                            Number = Convert.ToInt32(sdr["Number"])
                        };
                        list.Add(model);
                    }

                    bs.Data = list;
                }
                else
                {
                    bs.Code = -1;
                    bs.Message = res.Message;
                }
            }
            catch (Exception ex)
            {
                bs.Code = -1;
                bs.Message = ex.Message;
            }
            return bs;
        }




        public BaseStatus GetDataSet(string Server, string DataBaseName, string UserName, string PassWord,List<string> TableList)
        {
            BaseStatus bs = new BaseStatus();
            try
            {
                var res = OpenConn(Server, DataBaseName, UserName, PassWord);
                if (res.Code==200)
                {
                    DataSet ds = new DataSet();
                    foreach (var item in TableList)
                    {
                        string sql = "select top(1)* from "+ item + "";
                        SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                        sda.Fill(ds, item);

                        bs.Data = ds;
                    }
                }
                else
                {
                    bs.Code = -1;
                    bs.Message = res.Message;
                }
            }
            catch (Exception ex)
            {
                bs.Code = -1;
                bs.Message = ex.Message;
            }
            return bs;
        }

        public BaseStatus GetDataTable(string Server, string DataBaseName, string UserName, string PassWord,string TableName)
        {
            BaseStatus bs = new BaseStatus();
            try
            {
                var res = OpenConn(Server, DataBaseName, UserName, PassWord);
                if (res.Code == 200)
                {
                    DataTable dt = new DataTable();
                    string sql = "select top(1)* from " + TableName + "";
                    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                    sda.Fill(dt);

                    bs.Data = dt;
                }
                else
                {
                    bs.Code = -1;
                    bs.Message = res.Message;
                }
            }
            catch (Exception ex)
            {
                bs.Code = -1;
                bs.Message = ex.Message;
            }
            return bs;
        }
    }
}
